﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;

namespace DataProcess
{
    public partial class frmAddBrandName : Form
    {
        OleDbConnection clientConn = new OleDbConnection();
        SqlConnection serverConn = new SqlConnection();
        private int processIndex = 0;
        string logFile = Path.Combine(Application.StartupPath, string.Format("批量增加名称日志_{0}.log", DateTime.Now.ToString("yyyy-MM-dd_hh_mm_ss")));
        TextWriterTraceListener writer;
        public frmAddBrandName()
        {
            InitializeComponent();
            bool canlog = false;
            try
            {
                var stream = File.Open(logFile, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                stream.Dispose();
                canlog = true;
            }
            catch (Exception)
            {
                MessageBox.Show("无法读写日志文件！");
            }
            writer = new TextWriterTraceListener(logFile);

            Trace.Listeners.Add(writer);
            Trace.AutoFlush = true;
            Trace.TraceInformation("软件启动在：" + DateTime.Now.ToString());
            if (Properties.Settings.Default.mcdnms_Integrated)
            {
                label4.Visible = false;
                label5.Visible = false;
                tb_pwd.Visible = false;
                tb_user.Visible = false;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
            builder.Provider = "Microsoft.ACE.OLEDB.12.0";
            string cString = textBox1.Text;
            if (String.IsNullOrEmpty(cString))
            {
                MessageBox.Show("请选择数据库文件！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (cString.IndexOfAny(new char[] { '/', '\\' }) == -1)
            {
                cString = Path.Combine(Application.StartupPath, cString);
            }
            if (!File.Exists(cString))
            {
                MessageBox.Show("数据库文件不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            builder.DataSource = cString;
            clientConn.Close();
            clientConn.ConnectionString = builder.ConnectionString;
            try
            {
                clientConn.Open();
            }
            catch (Exception)
            {
                MessageBox.Show("不能打开数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (clientConn.State != ConnectionState.Open)
            {
                MessageBox.Show("不能打开数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            serverConn.Close();
            if (!Properties.Settings.Default.mcdnms_Integrated)
            {
                serverConn.ConnectionString = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", Properties.Settings.Default.mcdnms_server, Properties.Settings.Default.mcdnms_database, tb_user.Text, tb_pwd.Text);
            }
            else
            {
                serverConn.ConnectionString = String.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;Integrated Security=True", Properties.Settings.Default.mcdnms_server, Properties.Settings.Default.mcdnms_database);
            }
            try
            {
                serverConn.Open();
            }
            catch (Exception)
            {
                MessageBox.Show("不能打开服务端数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (serverConn.State != ConnectionState.Open)
            {
                MessageBox.Show("不能打开服务端数据库！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            backgroundWorker1.RunWorkerAsync(new object[] { clientConn, serverConn });
            button1.Enabled = false;
            button2.Enabled = false;
            textBox1.Enabled = false;
            button3.Enabled = true;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (backgroundWorker1.IsBusy)
            {
                backgroundWorker1.CancelAsync();
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            var bw = sender as BackgroundWorker;
            var argument = e.Argument as object[];
            var cConn = argument[0] as OleDbConnection;
            var sConn = argument[1] as SqlConnection;
            if (sConn.State != ConnectionState.Open)
            {
                sConn.Open();
            }

            using (var cmd = cConn.CreateCommand())
            {
                if (cConn.State != ConnectionState.Open)
                {
                    cConn.Open();
                }
                var schema = cConn.GetSchema("Columns", new string[] { null, null, "批量添加名称" });

                var scriptBuilder = new List<string>();
                var dropScript = new List<string>();
                if (schema.Select("COLUMN_NAME='pkid'").Length == 0)
                {
                    scriptBuilder.Add("pkid autoincrement,primary key (pkid)");
                }
                if (schema.Select("COLUMN_NAME='问题'").Length == 1)
                {
                    dropScript.Add("问题");
                }
                if (schema.Select("COLUMN_NAME='未添加药物组'").Length == 1)
                {
                    dropScript.Add("未添加药物组");
                }
                if (schema.Select("COLUMN_NAME='未添加商品名'").Length == 1)
                {
                    dropScript.Add("未添加商品名");
                }
                if (schema.Select("COLUMN_NAME='未添加剂型'").Length == 1)
                {
                    dropScript.Add("未添加剂型");
                }
                if (schema.Select("COLUMN_NAME='未添加关系'").Length == 1)
                {
                    dropScript.Add("未添加关系");
                }

                if (dropScript.Count > 0)
                {
                    cmd.CommandText = "ALTER TABLE 批量添加名称 DROP COLUMN " + string.Join(",", dropScript);
                    cmd.ExecuteNonQuery();
                }
                scriptBuilder.Add("问题 Text(255)");
                scriptBuilder.Add("未添加药物组 Smallint");
                scriptBuilder.Add("未添加商品名 Smallint");
                scriptBuilder.Add("未添加剂型 Smallint");
                scriptBuilder.Add("未添加关系 Smallint");
                if (scriptBuilder.Count > 0)
                {
                    cmd.CommandText = "ALTER TABLE 批量添加名称 ADD COLUMN " + string.Join(",", scriptBuilder);
                    cmd.ExecuteNonQuery();
                }
            }

            #region Adapters
            var adapter_Names = new addnamesDataSetTableAdapters.批量添加名称TableAdapter();

            #endregion

            adapter_Names.Connection = cConn;
            int brandNextKey, dispensableNextKey, brand_dispenseid, genericNextKey;

            var nameData = adapter_Names.GetData();
            processIndex = 0;
            int count = nameData.Count;
            foreach (var name in nameData)
            {
                if (bw.CancellationPending)
                {
                    e.Cancel = true; return;
                }
                processIndex++;
                #region 加通用药物组名称
                if (string.IsNullOrWhiteSpace(name.通用组名称))
                {
                    name.未添加药物组 = 1;
                    name.问题 = string.Format("药物组名称为空（pkid:{0}）,已跳过", name.pkid);
                    Trace.TraceError(name.问题);
                    adapter_Names.Update(name);
                    continue;
                }
                if (string.IsNullOrWhiteSpace(name.通用组名称拼音码))
                {
                    name.未添加药物组 = 1;
                    name.问题 = string.Format("药物组拼音码为空（药物组:{0}）,已跳过", name.通用组名称);
                    Trace.TraceError(name.问题);
                    adapter_Names.Update(name);
                    continue;
                }
                genericNextKey = GetGeneric(name.通用组名称, name.通用组英文名称, name.通用组名称拼音码, sConn, name);
                if (genericNextKey == 0)
                {
                    adapter_Names.Update(name);
                    continue;
                }
                #endregion
                #region 加商品名
                if (string.IsNullOrWhiteSpace(name.药物名称))
                {
                    name.未添加商品名 = 1;
                    name.问题 = string.Format("药物名称为空（pkid:{0}）,已跳过", name.pkid);
                    Trace.TraceError(name.问题);
                    adapter_Names.Update(name);
                    continue;
                }
                if (name.名称属性 != "中文其它名称" && name.名称属性 != "英文其它名称" && name.名称属性 != "中文通用名称" && name.名称属性 != "英文通用名称")
                {
                    name.未添加商品名 = 1;
                    name.问题 = string.Format("药物名称属性错误（pkid:{0}；药物名称：{1}）,已跳过", name.pkid, name.药物名称);
                    Trace.TraceError(name.问题);
                    adapter_Names.Update(name);
                    continue;
                }
                if (name.名称属性 == "中文其它名称" && string.IsNullOrWhiteSpace(name.通用组名称拼音码))
                {
                    name.未添加商品名 = 1;
                    name.问题 = string.Format("药物名称拼音码为空（pkid:{0}；药物名称：{1}）,已跳过", name.pkid, name.药物名称);
                    Trace.TraceError(name.问题);
                    adapter_Names.Update(name);
                    continue;
                }
                brandNextKey = GetBrand(genericNextKey, name.药物名称, name.药物名称拼音码, name.名称属性, sConn, name);
                if (brandNextKey == 0)
                {
                    adapter_Names.Update(name);
                    continue;
                }

                #endregion
                #region 加可调配
                if (string.IsNullOrWhiteSpace(name.剂型))
                {
                    name.未添加剂型 = 1;
                    name.问题 = string.Format("剂型为空（pkid:{0}）,已跳过", name.pkid);
                    Trace.TraceError(name.问题);
                    adapter_Names.Update(name);
                    continue;
                }
                dispensableNextKey = GetDispense(genericNextKey, name.剂型, name.规格, sConn, name);
                if (dispensableNextKey == 0)
                {
                    adapter_Names.Update(name);
                    continue;
                }
                #endregion
                #region 加对应关系
                brand_dispenseid = GetBrand_Dispense(brandNextKey, dispensableNextKey, name.剂型, sConn, name);
                if (brand_dispenseid == 0)
                {
                    adapter_Names.Update(name);
                    continue;
                }
                #endregion
                adapter_Names.Update(name);
                bw.ReportProgress((int)((double)processIndex * 100 / (double)count), string.Format("正在处理数据：{0}/{1}", processIndex, count));

            }
            bw.ReportProgress(100);
        }

        private int GetGeneric(string cname, string ename, string searchcode, SqlConnection conn, addnamesDataSet.批量添加名称Row row)
        {
            int genid = 0;
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@cname", cname);
                cmd.CommandText = "SELECT i_generic_drug, c_cname, c_search_code, c_ename, bt_is_abolish FROM dbo.dn_generic_drug WHERE (c_cname =@cname)";
                var reader = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(reader);
                reader.Dispose();
                if (dt.Rows.Count == 0)
                {
                    genid = AddGeneric(cname, ename, searchcode, conn, row);
                }
                else
                {
                    bool is_abolish = true;
                    if (dt.Rows.Count == 1)
                    {
                        is_abolish = Convert.ToBoolean(dt.Rows[0]["bt_is_abolish"]);
                        genid = Convert.ToInt32(dt.Rows[0]["i_generic_drug"]);
                    }
                    else
                    {
                        var unabolish = dt.Select("bt_is_abolish=0");
                        if (unabolish.Length == 0)
                        {
                            row.问题 = string.Format("找到多个已作废的药物组（药物组:{0}）,已跳过", cname, genid);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        if (unabolish.Length > 1)
                        {
                            row.问题 = string.Format("找到多个未作废的药物组（药物组:{0}）,已跳过", cname, genid);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        if (unabolish.Length == 1)
                        {
                            is_abolish = false;
                            genid = Convert.ToInt32(unabolish[0]["i_generic_drug"]);
                        }
                    }
                    if (is_abolish)
                    {
                        row.问题 = string.Format("药物组已作废（药物组:{0}）,已跳过", cname, genid);
                        Trace.TraceError(row.问题);
                        return 0;
                    }
                    Trace.TraceInformation("找到已存在的药物组：{0}", cname);
                    row.未添加药物组 = 1;
                    if (!is_abolish && string.IsNullOrEmpty(dt.Rows[0]["c_ename"] as string) && !string.IsNullOrWhiteSpace(ename))
                    {
                        using (var tran = conn.BeginTransaction())
                        {
                            cmd.Parameters.Clear();
                            cmd.Transaction = tran;
                            cmd.Parameters.AddWithValue("@ename", ename);
                            cmd.Parameters.AddWithValue("@genid", genid);
                            cmd.CommandText = "UPDATE [dn_generic_drug] SET [c_ename]=@ename WHERE [i_generic_drug]=@genid";
                            try
                            {
                                cmd.ExecuteNonQuery();
                                AddOtherName(genid, ename, null, "英文通用名称", cmd);
                                tran.Commit();
                            }
                            catch (Exception ex)
                            {
                                tran.Rollback();
                                Trace.TraceError("更新英文名失败:genid=" + genid + ";ename=" + ename + ";" + ex.ToString());
                            }
                        }
                    }
                }
                return genid;
            }
        }

        private int AddGeneric(string cname, string ename, string searchcode, SqlConnection conn, addnamesDataSet.批量添加名称Row row)
        {
            using (var tran = conn.BeginTransaction())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Transaction = tran;
                    try
                    {
                        cmd.CommandText = "SELECT [inextkey] FROM [mc_dn1_tablekeyrec] WHERE [stablename]='dn_generic_drug'";

                        int genid = Convert.ToInt32(cmd.ExecuteScalar()) + 1;

                        cmd.CommandText = "INSERT INTO dn_generic_drug (i_generic_drug, c_cname, c_search_code, c_ename) VALUES (@genid, @cname, @searchcode, @ename)";
                        cmd.Parameters.AddWithValue("@genid", genid);
                        cmd.Parameters.AddWithValue("@cname", cname);
                        cmd.Parameters.AddWithValue("@searchcode", searchcode);
                        cmd.Parameters.AddWithValue("@ename", ename);

                        cmd.ExecuteNonQuery();

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@key", genid);
                        cmd.CommandText = "UPDATE [mc_dn1_tablekeyrec] SET [inextkey] = @key WHERE [stablename]='dn_generic_drug'";
                        cmd.ExecuteNonQuery();

                        AddOtherName(genid, cname, searchcode, "中文通用名称", cmd);
                        if (!string.IsNullOrWhiteSpace(ename))
                        {
                            AddOtherName(genid, ename, null, "英文通用名称", cmd);
                        }

                        tran.Commit();
                        Trace.TraceInformation("添加药物组中文名：{0}", cname);
                        return genid;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        row.未添加药物组 = 1;
                        row.问题 = string.Format("添加药物组失败（药物组:{0}）,已跳过", cname);
                        Trace.TraceError(row.问题 + ex.ToString());
                        return 0;
                    }
                }
            }
        }

        private int GetBrand(int genid, string drugname, string searchcode, string nametype, SqlConnection conn, addnamesDataSet.批量添加名称Row row)
        {
            int brandid = 0;
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                #region 判断别名表
                cmd.Parameters.AddWithValue("@drugname", drugname);
                cmd.CommandText = "SELECT [i_other_name] ,[i_generic_drug] ,[c_name] ,[c_name_type] ,[bt_is_abolish] FROM [dn_other_name] WHERE [c_name]=@drugname";
                DataTable dt = new DataTable();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                if (dt.Rows.Count == 0)
                {
                    try
                    {
                        AddOtherName(genid, drugname, searchcode, nametype, cmd);
                    }
                    catch (Exception ex)
                    {
                        row.未添加商品名 = 1;
                        row.问题 = string.Format("别名表中添加名称失败（药物名称：{0})，已跳过", drugname);
                        Trace.TraceError(row.问题 + ex.ToString());
                        return 0;
                    }
                }
                else
                {
                    bool is_abolish = true;
                    int existsGenid;
                    string name_type;
                    if (dt.Rows.Count == 1)
                    {
                        existsGenid = Convert.ToInt32(dt.Rows[0]["i_generic_drug"]);
                        is_abolish = Convert.ToBoolean(dt.Rows[0]["bt_is_abolish"]);
                        name_type = dt.Rows[0]["c_name_type"].ToString();
                    }
                    else
                    {
                        var unabolish = dt.Select("bt_is_abolish=0");
                        if (unabolish.Length == 0)
                        {
                            row.未添加商品名 = 1;
                            row.问题 = string.Format("别名表找到多个已作废的别名（药物名称：{0}", drugname);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        if (unabolish.Length > 1)
                        {
                            row.未添加商品名 = 1;
                            row.问题 = string.Format("别名表找到多个未作废的别名（药物名称：{0}", drugname);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        else
                        {
                            is_abolish = false;
                            existsGenid = Convert.ToInt32(unabolish[0]["i_generic_drug"]);
                            name_type = unabolish[0]["c_name_type"].ToString();
                        }
                    }
                    if (existsGenid != genid)
                    {
                        row.未添加商品名 = 1;
                        row.问题 = string.Format("别名表中对应的药物组ID不同（药物名称：{0}；新genid:{1}；旧genid:{2})", drugname, genid, existsGenid);
                        Trace.TraceError(row.问题);
                        return 0;
                    }
                    if (name_type != row.名称属性)
                    {
                        row.未添加商品名 = 1;
                        row.问题 = string.Format("别名表中对应的名称属性不一致（药物名称：{0}；名称属性:{1})", drugname, name_type);
                        Trace.TraceError(row.问题);
                        return 0;
                    }
                    row.未添加商品名 = 1;
                    if (is_abolish)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@otherid", dt.Rows[0]["i_other_name"]);
                        cmd.CommandText = "UPDATE [dn_other_name] SET [bt_is_abolish] = 0 WHERE [i_other_name]=@otherid";
                        cmd.ExecuteNonQuery();
                    }
                }
                #endregion

                #region 添加商品名
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@drugname", drugname);
                cmd.CommandText = "SELECT i_brand_name, i_generic_drug, c_cname, c_ename,bt_is_abolish FROM dn_brand_name WHERE c_cname = @drugname";
                dt = new DataTable();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                if (dt.Rows.Count == 0)
                {
                    cmd.Parameters.Clear();
                    if (!nametype.StartsWith("中文") && !nametype.StartsWith("英文"))
                    {
                        row.未添加商品名 = 1;
                        row.问题 = string.Format("添加商品名失败（无法区别中文和英文名称）,已跳过", drugname, genid);
                        return 0;
                    }
                    #region 添加商品名
                    cmd.CommandText = "SELECT [inextkey] FROM [mc_dn1_tablekeyrec] WHERE [stablename]='dn_brand_name'";
                    brandid = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                    cmd.Parameters.AddWithValue("@brandid", brandid);
                    cmd.Parameters.AddWithValue("@genid", genid);
                    cmd.Parameters.AddWithValue("@drugname", nametype.StartsWith("中文") ? drugname : string.Empty);
                    cmd.Parameters.AddWithValue("@searchcode", searchcode);
                    cmd.Parameters.AddWithValue("@ename", nametype.StartsWith("英文") ? drugname : string.Empty);
                    cmd.CommandText = "INSERT INTO dn_brand_name (i_brand_name, i_generic_drug, c_cname, c_search_code, c_ename) VALUES (@brandid,@genid, @drugname, @searchcode, @ename)";
                    using (var tran = conn.BeginTransaction())
                    {
                        cmd.Transaction = tran;
                        try
                        {
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@key", brandid);
                            cmd.CommandText = "UPDATE [mc_dn1_tablekeyrec] SET [inextkey] = @key WHERE [stablename]='dn_brand_name'";
                            cmd.ExecuteNonQuery();
                            tran.Commit();
                            Trace.TraceInformation("添加商品名：{0}；药物组ID：{1}", drugname, genid);
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            row.未添加商品名 = 1;
                            row.问题 = string.Format("添加商品名失败（商品名:{0}；药物组ID:{1}）,已跳过", drugname, genid);
                            Trace.TraceError(row.问题 + ex.ToString());
                            return 0;
                        }
                    }
                    #endregion
                }
                else
                {
                    bool is_abolish = true;
                    int existsGenid;
                    if (dt.Rows.Count == 1)
                    {
                        brandid = Convert.ToInt32(dt.Rows[0]["i_brand_name"]);
                        existsGenid = Convert.ToInt32(dt.Rows[0]["i_generic_drug"]);
                        is_abolish = Convert.ToBoolean(dt.Rows[0]["bt_is_abolish"]);

                    }
                    else
                    {
                        var unabolish = dt.Select("bt_is_abolish=0");
                        if (unabolish.Length == 0)
                        {
                            row.问题 = string.Format("找到多个已作废的商品名（药物名称：{0}", drugname);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        if (unabolish.Length == 1)
                        {
                            is_abolish = false;
                            brandid = Convert.ToInt32(unabolish[0]["i_brand_name"]);
                            existsGenid = Convert.ToInt32(unabolish[0]["i_generic_drug"]);
                        }
                        else
                        {
                            row.问题 = string.Format("找到多个未作废的商品名（药物名称：{0}）", drugname);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                    }

                    if (existsGenid != genid)
                    {
                        row.问题 = string.Format("商品名中对应的药物组ID不同（药物名称：{0}；新genid:{1}；旧genid:{2})", drugname, genid, existsGenid);
                        Trace.TraceError(row.问题);
                        return 0;
                    }
                    Trace.TraceInformation("找到已存在的商品名：{0}；药物组ID:{1}；作废标记：{2}", drugname, genid, is_abolish);
                    row.未添加商品名 = 1;
                    if (is_abolish)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@brandid", brandid);
                        cmd.CommandText = "UPDATE dn_brand_name SET bt_is_abolish=0 WHERE i_brand_name=@brandid";
                        cmd.ExecuteNonQuery();
                    }

                }
                #endregion
            }
            return brandid;
        }

        private int GetDispense(int genid, string dosform, string strength, SqlConnection conn, addnamesDataSet.批量添加名称Row row)
        {
            int dispenseid = 0;
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@genid", genid);
                cmd.Parameters.AddWithValue("@dosform", dosform);
                cmd.Parameters.AddWithValue("@strength", strength);
                cmd.CommandText = "SELECT i_gd_dispensable, bt_is_abolish FROM dn_gd_dispensable WHERE (i_generic_drug = @genid) AND (c_doseform = @dosform) AND (c_show_strength = @strength)";
                var dt = new DataTable();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                if (dt.Rows.Count == 0)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = "SELECT [inextkey] FROM [mc_dn1_tablekeyrec] WHERE [stablename]='dn_gd_dispensable'";
                    dispenseid = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                    cmd.Parameters.AddWithValue("@genid", genid);
                    cmd.Parameters.AddWithValue("@dosform", dosform);
                    cmd.Parameters.AddWithValue("@strength", strength);
                    cmd.Parameters.AddWithValue("@dispenseid", dispenseid);
                    cmd.CommandText = "INSERT INTO dn_gd_dispensable (i_gd_dispensable, i_generic_drug, c_doseform,c_show_strength,c_spec,c_spec_unit) VALUES (@dispenseid, @genid, @dosform,@strength,'','')";
                    using (var tran = conn.BeginTransaction())
                    {
                        cmd.Transaction = tran;
                        try
                        {
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@key", dispenseid);
                            cmd.CommandText = "UPDATE [mc_dn1_tablekeyrec] SET [inextkey] = @key WHERE [stablename]='dn_gd_dispensable'";
                            cmd.ExecuteNonQuery();
                            tran.Commit();
                            Trace.TraceInformation("添加调配剂型：{0}；药物组ID：{1}", dosform, genid);
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            row.未添加剂型 = 1;
                            row.问题 = string.Format("添加调配剂型失败（剂型:{0}；药物组ID:{1}）,已跳过", dosform, genid);
                            Trace.TraceError(row.问题 + ex.ToString());
                            return 0;
                        }

                    }
                }
                else
                {
                    bool is_abolish = true;
                    if (dt.Rows.Count == 1)
                    {
                        dispenseid = Convert.ToInt32(dt.Rows[0]["i_gd_dispensable"]);
                        is_abolish = Convert.ToBoolean(dt.Rows[0]["bt_is_abolish"]);
                    }
                    else
                    {
                        var unabolish = dt.Select("bt_is_abolish=0");
                        if (unabolish.Length == 0)
                        {
                            row.问题 = string.Format("找到多条已作废的剂型（剂型:{0}；药物组ID:{1}）,已跳过", dosform, genid);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        if (unabolish.Length > 1)
                        {
                            row.问题 = string.Format("找到多条未作废的剂型（剂型:{0}；药物组ID:{1}）,已跳过", dosform, genid);
                            Trace.TraceError(row.问题);
                            return 0;
                        }
                        else
                        {
                            is_abolish = false;
                            dispenseid = Convert.ToInt32(unabolish[0]["i_gd_dispensable"]);
                        }
                    }

                    Trace.TraceInformation("找到已存在的剂型：{0}；药物组ID:{1}；作废标记：{2}", dosform, genid, is_abolish);
                    row.未添加剂型 = 1;
                    if (is_abolish)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@dispenseid", dispenseid);
                        cmd.CommandText = "UPDATE dn_gd_dispensable SET bt_is_abolish=0 WHERE i_gd_dispensable=@dispenseid";
                        cmd.ExecuteNonQuery();
                    }
                }

            }
            return dispenseid;
        }

        private int GetBrand_Dispense(int brandid, int dispenseid, string dosform, SqlConnection conn, addnamesDataSet.批量添加名称Row row)
        {
            int brand_dispenseid = 0;
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@brandid", brandid);
                cmd.Parameters.AddWithValue("@dispenseid", dispenseid);
                cmd.CommandText = "SELECT [i_bn_dispensable],bt_is_abolish FROM [dn_bn_dispensable] WHERE (i_brand_name = @brandid) AND (i_gd_dispensable = @dispenseid)";
                var dt = new DataTable();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
                if (dt.Rows.Count == 0)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = "SELECT [inextkey] FROM [mc_dn1_tablekeyrec] WHERE [stablename]='dn_bn_dispensable'";
                    brand_dispenseid = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                    cmd.Parameters.AddWithValue("@brandid", brandid);
                    cmd.Parameters.AddWithValue("@dosform", dosform);
                    cmd.Parameters.AddWithValue("@dispenseid", dispenseid);
                    cmd.Parameters.AddWithValue("@brand_dispenseid", brand_dispenseid);
                    cmd.CommandText = "INSERT INTO [dn_bn_dispensable]([i_bn_dispensable],[i_brand_name],[i_gd_dispensable],[c_doseform]) VALUES (@brand_dispenseid,@brandid,@dispenseid,@dosform)";
                    using (var tran = conn.BeginTransaction())
                    {
                        cmd.Transaction = tran;
                        try
                        {
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@key", brand_dispenseid);
                            cmd.CommandText = "UPDATE [mc_dn1_tablekeyrec] SET [inextkey] = @key WHERE [stablename]='dn_bn_dispensable'";
                            cmd.ExecuteNonQuery();
                            tran.Commit();
                            Trace.TraceInformation("添加关系：{0}-{1}", brandid, dispenseid);
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            row.未添加关系 = 1;
                            row.问题 = string.Format("添加关系失败（调配剂型ID:{0}；商品名ID:{1}）,已跳过", dispenseid, brandid);
                            Trace.TraceError(row.问题 + ex.ToString());
                            return 0;
                        }

                    }
                }
                else
                {
                    brand_dispenseid = Convert.ToInt32(dt.Rows[0]["i_bn_dispensable"]);
                    bool is_abolish = Convert.ToBoolean(dt.Rows[0]["bt_is_abolish"]);
                    Trace.TraceInformation("找到已存在的关系：brandid:{0}；dispenseid:{1}，作废：{2}", brandid, dispenseid, is_abolish);
                    row.未添加关系 = 1;
                    if (is_abolish)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@brand_dispenseid", brand_dispenseid);
                        cmd.CommandText = "UPDATE dn_bn_dispensable SET bt_is_abolish=0 WHERE i_bn_dispensable=@brand_dispenseid";
                        cmd.ExecuteNonQuery();
                    }
                }

            }
            return brand_dispenseid;
        }

        private int AddOtherName(int genid, string cname, string searchcode, string nametype, SqlCommand cmd)
        {
            cmd.Parameters.Clear();
            cmd.CommandText = "SELECT [inextkey] FROM [mc_dn1_tablekeyrec] WHERE [stablename]='dn_other_name'";
            int otherid = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
            cmd.Parameters.AddWithValue("@genid", genid);
            cmd.Parameters.AddWithValue("@otherid", otherid);
            cmd.Parameters.AddWithValue("@cname", cname);
            cmd.Parameters.AddWithValue("@nametype", nametype);
            if (string.IsNullOrEmpty(searchcode))
            {
                cmd.CommandText = "INSERT INTO dn_other_name (i_other_name, i_generic_drug, c_name, c_name_type) VALUES (@otherid, @genid, @cname, @nametype)";
            }
            else
            {
                cmd.Parameters.AddWithValue("@searchcode", searchcode);
                cmd.CommandText = "INSERT INTO dn_other_name (i_other_name, i_generic_drug, c_name, c_search_code, c_name_type) VALUES (@otherid, @genid, @cname, @searchcode, @nametype)";
            }


            cmd.ExecuteNonQuery();

            #region 更新otherid
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@key", otherid);
            cmd.CommandText = "UPDATE [mc_dn1_tablekeyrec] SET [inextkey] = @key WHERE [stablename]='dn_other_name'";
            cmd.ExecuteNonQuery();
            #endregion
            Trace.TraceInformation("添加别名名：{0}；类型：{1}", cname, nametype);
            return otherid;

        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Value = e.ProgressPercentage;
            if (e.UserState != null)
            {
                label2.Text = e.UserState.ToString();
            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (clientConn.State == ConnectionState.Open)
            {
                clientConn.Close();
            }
            if (serverConn.State == ConnectionState.Open)
            {
                serverConn.Close();
            }
            string msg = string.Format("处理完成，共处理了{0}条数据\n\n处理结果请查看access表中的日志", processIndex);
            if (e.Cancelled)
            {
                msg = string.Format("用户取消导入，共处理了{0}条数据", processIndex);
            }
            if (e.Error != null)
            {
                msg = string.Format("导入时发生了错误，共处理了{0}条数据", processIndex);
                Trace.TraceError(e.Error.ToString());
            }

            Trace.TraceInformation(msg);
            writer.Dispose();
            MessageBox.Show(msg, "完成");
            Process logProcess = new Process();
            logProcess.StartInfo.FileName = logFile;
            logProcess.Start();
            logProcess.Dispose();
            button1.Enabled = true;
            button2.Enabled = true;
            textBox1.Enabled = true;
            button3.Enabled = false;
        }
    }
}
